﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Lines
{
    internal static class LineDAL
    {
        internal static LineEntity GetLineByReader(IDataReader dr)
        {
            LineEntity line = new LineEntity();

            line.LineID = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
            line.ShopID = dr.IsDBNull(1) ? 0 : dr.GetInt32(1);
            line.Name = dr.IsDBNull(2) ? null : dr.GetString(2);
            line.DestinationProvinceID = dr.IsDBNull(3) ? 0 : dr.GetInt32(3);
            line.DestinationCityID = dr.IsDBNull(4) ? 0 : dr.GetInt32(4);
            line.DestinationAreaID = dr.IsDBNull(5) ? 0 : dr.GetInt32(5);
            line.StagingProvinceID = dr.IsDBNull(6) ? 0 : dr.GetInt32(6);
            line.StagingCityID = dr.IsDBNull(7) ? 0 : dr.GetInt32(7);
            line.StagingAreaID = dr.IsDBNull(8) ? 0 : dr.GetInt32(8);
            line.Type = dr.IsDBNull(9) ? 0 : dr.GetInt32(9);
            line.Days = dr.IsDBNull(10) ? 0 : dr.GetInt32(10);
            line.Holiday = dr.IsDBNull(11) ? 0 : dr.GetInt32(11);
            line.Stay = dr.IsDBNull(12) ? 0 : dr.GetInt32(12);
            line.Grade = dr.IsDBNull(13) ? 0 : dr.GetInt32(13);
            line.Price = dr.IsDBNull(14) ? 0 : dr.GetDecimal(14);
            line.LineFeatures = dr.IsDBNull(15) ? null : dr.GetString(15);
            line.ReferenceStroke = dr.IsDBNull(16) ? null : dr.GetString(16);
            line.CostDescription = dr.IsDBNull(17) ? null : dr.GetString(17);
            line.Precautions = dr.IsDBNull(18) ? null : dr.GetString(18);
            line.Hits = dr.IsDBNull(19) ? 0 : dr.GetInt32(19);
            line.Created = Util.ConverDateTime(dr.GetValue(20));
            line.IsEnable = dr.IsDBNull(21) ? false : dr.GetBoolean(21);
            line.ShopName = dr.IsDBNull(22) ? null : dr.GetString(22);
            line.MemberCount = dr.IsDBNull(23) ? 0 : dr.GetInt32(23);

            return line;
        }

        internal static int GetLineCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectLine = @"SELECT COUNT([Line].[LineID]) 
                FROM [Line] LEFT OUTER JOIN Shop ON Line.ShopID = Shop.ShopID 
                    LEFT OUTER JOIN Area AS DesProvinceArea ON [Line].DestinationProvinceID = DesProvinceArea.AreaID 
                    LEFT OUTER JOIN Area AS StaProvinceArea ON [Line].StagingProvinceID = StaProvinceArea.AreaID";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectLine, ref parameterlst);
            DbCommand SelectLineCommand = db.GetSqlStringCommand(SelectLine);
            DbHelper.PackDbCommandByDbParameters(SelectLineCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectLineCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<LineEntity> GetLineList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<LineEntity> linelst = new List<LineEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectLine = @"
                    SELECT [LineID],[ShopID],[Name],[DestinationProvinceID],[DestinationCityID],[DestinationAreaID],
                        [StagingProvinceID],[StagingCityID],[StagingAreaID],[Type],[Days],[Holiday],[Stay],[Grade],[Price],
                        [LineFeatures],[ReferenceStroke],[CostDescription],[Precautions],[Hits],[Created],[IsEnable],
                        [ShopName], dbo.F_GetLineMemberCount(Line.LineID) AS MemberCount,[DesProvinceName],[StaProvinceArea],dbo.F_GetPhotoUrl(Line.LineID,1,0) AS ImgUrl01,
                        dbo.F_GetDepartureDate(Line.LineID,0) AS DepartureDate
                    FROM (
                        SELECT [Line].[LineID],[Line].[ShopID],[Line].[Name],[Line].[DestinationProvinceID],[Line].[DestinationCityID],[Line].[DestinationAreaID],
                            [Line].[StagingProvinceID],[Line].[StagingCityID],[Line].[StagingAreaID],[Line].[Type],[Line].[Days],[Line].[Holiday],[Line].[Stay],[Line].[Grade],[Line].[Price],
                            [Line].[LineFeatures],[Line].[ReferenceStroke],[Line].[CostDescription],[Line].[Precautions],[Line].[Hits],[Line].[Created],[Line].[IsEnable], 
                            Shop.Name AS ShopName, DesProvinceArea.Name AS DesProvinceName, StaProvinceArea.Name AS StaProvinceArea,
                            ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectLine += " " + sortField + " " + sortMode + ",";
            SelectLine += @" [Line].[LineID] Desc
                    ) AS rowNum 
                        FROM [Line] LEFT OUTER JOIN Shop ON Line.ShopID = Shop.ShopID 
                            LEFT OUTER JOIN Area AS DesProvinceArea ON [Line].DestinationProvinceID = DesProvinceArea.AreaID 
                            LEFT OUTER JOIN Area AS StaProvinceArea ON [Line].StagingProvinceID = StaProvinceArea.AreaID ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectLine, ref parameterlst);
            SelectLine += @"
                    ) AS [Line] 
                    WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectLineCommand = db.GetSqlStringCommand(SelectLine);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectLineCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectLineCommand))
            {
                while (dr.Read())
                {
                    LineEntity line = GetLineByReader(dr);
                    line.DesProvinceName = (dr["DesProvinceName"] == DBNull.Value ? null : dr["DesProvinceName"].ToString());
                    line.StaProvinceArea = (dr["StaProvinceArea"] == DBNull.Value ? null : dr["StaProvinceArea"].ToString());
                    line.ImgUrl01 = string.IsNullOrEmpty(dr["ImgUrl01"].ToString()) ? "Null.jpg" : dr["ImgUrl01"].ToString();
                    line.DepartureDate = Util.ConverDateTime(dr["DepartureDate"]);
                    linelst.Add(line);
                }
            }

            return linelst;
        }

        internal static List<LineEntity> GetLineList(IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<LineEntity> linelst = new List<LineEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectLine = @"
                    SELECT [Line].[LineID],[Line].[ShopID],[Line].[Name],[Line].[DestinationProvinceID],[Line].[DestinationCityID],[Line].[DestinationAreaID],
                        [Line].[StagingProvinceID],[Line].[StagingCityID],[Line].[StagingAreaID],[Line].[Type],[Line].[Days],[Line].[Holiday],[Line].[Stay],[Line].[Grade],[Line].[Price],
                        [Line].[LineFeatures],[Line].[ReferenceStroke],[Line].[CostDescription],[Line].[Precautions],[Line].[Hits],[Line].[Created],[Line].[IsEnable],
                        Shop.Name AS ShopName, dbo.F_GetLineMemberCount(Line.LineID) AS MemberCount,DesProvinceArea.Name AS DesProvinceName, StaProvinceArea.Name AS StaProvinceArea, dbo.F_GetPhotoUrl(Line.LineID,1,0) AS ImgUrl01,
                        dbo.F_GetDepartureDate(Line.LineID,0) AS DepartureDate
                    FROM [Line] LEFT OUTER JOIN Shop ON Line.ShopID = Shop.ShopID
                        LEFT OUTER JOIN Area AS DesProvinceArea ON [Line].DestinationProvinceID = DesProvinceArea.AreaID 
                        LEFT OUTER JOIN Area AS StaProvinceArea ON [Line].StagingProvinceID = StaProvinceArea.AreaID ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectLine, ref parameterlst);
            SelectLine += " ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectLine += " " + sortField + " " + sortMode + ",";
            SelectLine += " [Line].[LineID] Desc ";

            DbCommand SelectLineCommand = db.GetSqlStringCommand(SelectLine);
            DbHelper.PackDbCommandByDbParameters(SelectLineCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectLineCommand))
            {
                while (dr.Read())
                {
                    LineEntity line = GetLineByReader(dr);
                    line.DesProvinceName = (dr["DesProvinceName"] == DBNull.Value ? null : dr["DesProvinceName"].ToString());
                    line.StaProvinceArea = (dr["StaProvinceArea"] == DBNull.Value ? null : dr["StaProvinceArea"].ToString());
                    line.ImgUrl01 = string.IsNullOrEmpty(dr["ImgUrl01"].ToString()) ? "Null.jpg" : dr["ImgUrl01"].ToString();
                    line.DepartureDate = Util.ConverDateTime(dr["DepartureDate"]);
                    linelst.Add(line);
                }
            }

            return linelst;
        }

        internal static LineEntity GetLine(int lineID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            LineEntity line = null;

            string SelectLine = @"
                    SELECT [Line].[LineID],[Line].[ShopID],[Line].[Name],[Line].[DestinationProvinceID],[Line].[DestinationCityID],[Line].[DestinationAreaID],
                        [Line].[StagingProvinceID],[Line].[StagingCityID],[Line].[StagingAreaID],[Line].[Type],[Line].[Days],[Line].[Holiday],[Line].[Stay],[Line].[Grade],[Line].[Price],
                        [Line].[LineFeatures],[Line].[ReferenceStroke],[Line].[CostDescription],[Line].[Precautions],[Line].[Hits],[Line].[Created],[Line].[IsEnable],Shop.Name AS ShopName, dbo.F_GetLineMemberCount(Line.LineID) AS MemberCount,
                        dbo.F_GetPhotoUrl(Line.LineID,1,0) AS ImgUrl01
                    FROM [Line] LEFT OUTER JOIN Shop ON Line.ShopID = Shop.ShopID
                    WHERE [Line].[LineID]=@LineID
                ";

            DbCommand SelectLineCommand = db.GetSqlStringCommand(SelectLine);
            db.AddInParameter(SelectLineCommand, "@LineID", DbType.Int32, lineID);

            using (IDataReader dr = db.ExecuteReader(SelectLineCommand))
            {
                if (dr.Read())
                {
                    line = GetLineByReader(dr);
                    line.ImgUrl01 = string.IsNullOrEmpty(dr["ImgUrl01"].ToString()) ? "Null.jpg" : dr["ImgUrl01"].ToString();
                }
            }

            return line;
        }

        internal static int InsertLine(LineEntity line)
        {
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder InsertLine = new StringBuilder();
            List<DbParameter> parameterlst = new List<DbParameter>();

            #region 插入线路
            InsertLine.Append(@"Declare @LineID int;
                INSERT INTO [Line] 
				([ShopID],[Name],[DestinationProvinceID],[DestinationCityID],[DestinationAreaID],[StagingProvinceID],
                [StagingCityID],[StagingAreaID],[Type],[Days],[Holiday],[Stay],[Grade],[Price],[LineFeatures],[ReferenceStroke],
                [CostDescription],[Precautions],[Hits],[Created],[IsEnable])
				 VALUES (@ShopID,@Name,@DestinationProvinceID,@DestinationCityID,@DestinationAreaID,@StagingProvinceID,
                @StagingCityID,@StagingAreaID,@Type,@Days,@Holiday,@Stay,@Grade,@Price,@LineFeatures,@ReferenceStroke,
                @CostDescription,@Precautions,@Hits,@Created,@IsEnable);
                SET @LineID=@@IDENTITY;");

            parameterlst.Add(DbHelper.PackInParameter(db, "@ShopID", DbType.Int32, line.ShopID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Name", DbType.String, line.Name));
            parameterlst.Add(DbHelper.PackInParameter(db, "@DestinationProvinceID", DbType.Int32, line.DestinationProvinceID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@DestinationCityID", DbType.Int32, line.DestinationCityID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@DestinationAreaID", DbType.Int32, line.DestinationAreaID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@StagingProvinceID", DbType.Int32, line.StagingProvinceID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@StagingCityID", DbType.Int32, line.StagingCityID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@StagingAreaID", DbType.Int32, line.StagingAreaID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Type", DbType.Int32, line.Type));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Days", DbType.Int32, line.Days));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Holiday", DbType.Int32, line.Holiday));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Stay", DbType.Int32, line.Stay));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Grade", DbType.Int32, line.Grade));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Price", DbType.Decimal, line.Price));
            parameterlst.Add(DbHelper.PackInParameter(db, "@LineFeatures", DbType.String, line.LineFeatures));
            parameterlst.Add(DbHelper.PackInParameter(db, "@ReferenceStroke", DbType.String, line.ReferenceStroke));
            parameterlst.Add(DbHelper.PackInParameter(db, "@CostDescription", DbType.String, line.CostDescription));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Precautions", DbType.String, line.Precautions));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Hits", DbType.Int32, line.Hits));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Created", DbType.DateTime, line.Created));
            parameterlst.Add(DbHelper.PackInParameter(db, "@IsEnable", DbType.Boolean, line.IsEnable));
            #endregion

            #region 插入线路图片
            string idn = string.Empty;
            for (int n = 0; n < line.ImgUrllst.Count; n++)
            {
                idn = "_Photo" + n;
                InsertLine.Append("INSERT INTO [Photo] ([ObjectID],[ObjectType],[ImgUrl]) VALUES (@LineID, 1, @ImgUrl").Append(idn).Append(");");
                parameterlst.Add(DbHelper.PackInParameter(db, "@ImgUrl" + idn, DbType.String, line.ImgUrllst[n]));
            }
            #endregion

            #region 插入出发日期
            idn = string.Empty;
            for (int n = 0; n < line.Schedulelst.Count; n++)
            {
                idn = "_Schedule" + n;
                InsertLine.Append("INSERT INTO [LineSchedule] ([LineID],[DepartureDate]) VALUES (@LineID, @DepartureDate").Append(idn).Append(");");
                parameterlst.Add(DbHelper.PackInParameter(db, "@DepartureDate" + idn, DbType.DateTime, line.Schedulelst[n].DepartureDate));
            }
            #endregion
            InsertLine.Append("SELECT @LineID;");

            DbCommand InsertLineCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_InsertLine", InsertLine.ToString()));
            DbHelper.PackDbCommandByDbParameters(InsertLineCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(InsertLineCommand);

            return obj == null ? 0 : (int)obj;
        }

        internal static int UpdateLine(LineEntity line)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder UpdateLine = new StringBuilder();
            List<DbParameter> parameterlst = new List<DbParameter>();

            #region 更新线路
            UpdateLine.Append(@"UPDATE [Line] 
				 SET [ShopID]=@ShopID,[Name]=@Name,[DestinationProvinceID]=@DestinationProvinceID,
                    [DestinationCityID]=@DestinationCityID,[DestinationAreaID]=@DestinationAreaID,
                    [StagingProvinceID]=@StagingProvinceID,[StagingCityID]=@StagingCityID,[StagingAreaID]=@StagingAreaID,
                    [Type]=@Type,[Days]=@Days,[Holiday]=@Holiday,[Stay]=@Stay,[Grade]=@Grade,[Price]=@Price,
                    [LineFeatures]=@LineFeatures,[ReferenceStroke]=@ReferenceStroke,[CostDescription]=@CostDescription,
                    [Precautions]=@Precautions,[Hits]=@Hits,[Created]=@Created,[IsEnable]=@IsEnable 
				 WHERE [LineID] = @LineID;");

            parameterlst.Add(DbHelper.PackInParameter(db, "@LineID", DbType.Int32, line.LineID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@ShopID", DbType.Int32, line.ShopID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Name", DbType.String, line.Name));
            parameterlst.Add(DbHelper.PackInParameter(db, "@DestinationProvinceID", DbType.Int32, line.DestinationProvinceID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@DestinationCityID", DbType.Int32, line.DestinationCityID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@DestinationAreaID", DbType.Int32, line.DestinationAreaID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@StagingProvinceID", DbType.Int32, line.StagingProvinceID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@StagingCityID", DbType.Int32, line.StagingCityID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@StagingAreaID", DbType.Int32, line.StagingAreaID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Type", DbType.Int32, line.Type));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Days", DbType.Int32, line.Days));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Holiday", DbType.Int32, line.Holiday));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Stay", DbType.Int32, line.Stay));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Grade", DbType.Int32, line.Grade));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Price", DbType.Decimal, line.Price));
            parameterlst.Add(DbHelper.PackInParameter(db, "@LineFeatures", DbType.String, line.LineFeatures));
            parameterlst.Add(DbHelper.PackInParameter(db, "@ReferenceStroke", DbType.String, line.ReferenceStroke));
            parameterlst.Add(DbHelper.PackInParameter(db, "@CostDescription", DbType.String, line.CostDescription));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Precautions", DbType.String, line.Precautions));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Hits", DbType.Int32, line.Hits));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Created", DbType.DateTime, line.Created));
            parameterlst.Add(DbHelper.PackInParameter(db, "@IsEnable", DbType.Boolean, line.IsEnable));
            #endregion

            #region 更新线路图片
            string idn = string.Empty;
            UpdateLine.Append("DELETE FROM [Photo] WHERE [ObjectID]=@LineID AND [ObjectType]=1;");
            for (int n = 0; n < line.ImgUrllst.Count; n++)
            {
                idn = "_Photo" + n;
                UpdateLine.Append("INSERT INTO [Photo] ([ObjectID],[ObjectType],[ImgUrl]) VALUES (@LineID, 1, @ImgUrl").Append(idn).Append(");");
                parameterlst.Add(DbHelper.PackInParameter(db, "@ImgUrl" + idn, DbType.String, line.ImgUrllst[n]));
            }
            #endregion

            #region  更新出发日期
            idn = string.Empty;
            UpdateLine.Append("DELETE FROM [LineSchedule] WHERE [LineID]=@LineID;");
            for (int n = 0; n < line.Schedulelst.Count; n++)
            {
                idn = "_Schedule" + n;
                UpdateLine.Append("INSERT INTO [LineSchedule] ([LineID],[DepartureDate]) VALUES (@LineID, @DepartureDate").Append(idn).Append(");");
                parameterlst.Add(DbHelper.PackInParameter(db, "@DepartureDate" + idn, DbType.DateTime, line.Schedulelst[n].DepartureDate));
            }
            #endregion

            DbCommand UpdateLineCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_UpdateLine", UpdateLine.ToString()));
            DbHelper.PackDbCommandByDbParameters(UpdateLineCommand, parameterlst.ToArray());

            result = db.ExecuteNonQuery(UpdateLineCommand);

            return result;
        }

        internal static int DeleteLine(int lineID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteLine = @"
                DELETE FROM [Line] WHERE LineID=@LineID;
                DELETE FROM [LineSchedule] WHERE LineID=@LineID;
                DELETE FROM [LineFeature] WHERE LineID=@LineID;
                DELETE FROM [Photo] WHERE ObjectID=@LineID AND ObjectType=1;";

            DbCommand DeleteLineCommand = db.GetSqlStringCommand(DeleteLine);
            db.AddInParameter(DeleteLineCommand, "@LineID", DbType.Int32, lineID);
            result = db.ExecuteNonQuery(DeleteLineCommand);

            return result;
        }

        internal static int DeleteLineByShopID(int lineID, int shopID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteLine = @"
                IF EXISTS(SELECT [LineID] FROM Line WHERE [LineID]=@LineID AND ShopID=@ShopID)
	            BEGIN
                    DELETE FROM [Line] WHERE LineID=@LineID;
                END";

            DbCommand DeleteLineCommand = db.GetSqlStringCommand(DeleteLine);
            db.AddInParameter(DeleteLineCommand, "@LineID", DbType.Int32, lineID);
            db.AddInParameter(DeleteLineCommand, "@ShopID", DbType.Int32, shopID);
            result = db.ExecuteNonQuery(DeleteLineCommand);

            return result;
        }

        internal static int SetLineEnable(int lineID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetLine = @"UPDATE [Line]
                    SET [IsEnable] = ([IsEnable]+1)%2
                    WHERE [LineID] = @LineID";

            DbCommand SetLineCommand = db.GetSqlStringCommand(SetLine);
            db.AddInParameter(SetLineCommand, "@LineID", DbType.Int32, lineID);
            result = db.ExecuteNonQuery(SetLineCommand);

            return result;
        }

        internal static int HitLine(int lineID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetLine = @"UPDATE [Line]
                    SET [Hits] = [Hits]+1
                    WHERE [LineID] = @LineID";

            DbCommand SetLineCommand = db.GetSqlStringCommand(SetLine);
            db.AddInParameter(SetLineCommand, "@LineID", DbType.Int32, lineID);
            result = db.ExecuteNonQuery(SetLineCommand);

            return result;
        }
    }
}
